package db;

import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.UUID;
import java.sql.SQLException;


import javax.servlet.http.Cookie;

import com.mysql.jdbc.CallableStatement;


@SuppressWarnings("unused")
public class DBHelper {

	
	// *************** CLASS VARIABLES ******************************************************************* 
	
	/** Database connection variable */
	Connection conn;

	
	// *************** PREPARED STATEMENT VARIABLES *******************************************************
	
	// *************** USER ****************** 
	
	// returns 1 if user valid, returns 0 if user does not valid
	/** Statement to validate user login */
	static String validateUserStatement = "SELECT IFNULL(COUNT(*),0) AS userLoginValid  FROM Customer "
			+ " where password = ? and PhoneNumber = ?";
	
	// returns 1 if user exists, returns 0 if user does not exist
	/** Statement to validate user does not already exist */
	static String validateUserDoesNotExistStatement = "SELECT IFNULL(COUNT(*),0) AS UserExist  FROM Customer "
			+ " where PhoneNumber = ? ";
	
	/** Statement to add new user */
	static String registerUserStatement = "INSERT INTO Customer(Name,PhoneNumber,EmailAddress,Password,birthDay,RoleTypes_idRoleTypes, CreatedDatetime, ModifiedDatetime)"
			+ " VALUES(?,?,?,?,?,?,?,?);";
	
	/** Statement to get user data */
	static String userDataStatement = "SELECT * FROM Customer WHERE PhoneNumber = ?";
	
	/*
	static String sDate = "2013-02-11 23:59:59";
	static String eDate = "2013-03-11 23:59:59";
	static String pointValue = "205";
	static String qryStringSdates = " and active = 0  and begindatetime >= '"
			+ sDate + "'";
	static String qryStringEdates = " and enddatetime <= '" + eDate + "'";
	static String qryStringPointValues = " and pointvalue between 1 and "
			+ pointValue;

	String defaultqryStringSdates = " and active = 0  and begindatetime >= begindatetime";
	String defaultqryStringEdates = " and enddatetime <= enddatetime";
	String defaultqryStringPointValues = " and pointvalue between 1 and 200 ";
	
	
	static String getRewardsDataStatement = "SELECT * FROM Coupons where 1 = 1 "
			+ qryStringSdates + qryStringEdates
			+ qryStringPointValues + " order by idCoupons asc";
	*/
	
	/** Statement to get coupons data */
	static String getCouponsDataStatement = "SELECT * FROM Coupons where 1 = 1";
	
	/** Statement to get role data */
	static String roleDataStatement = "SELECT * FROM RoleTypes";
	
	/** Statement check user out */
	static String checkUserOut = "DELETE from CheckInOut where idCheckInOut = ?";
	
	/** Statement to check user in */
	static String checkUserInStatement = "INSERT INTO CheckInOut"
			+ "(Customer_idCustomer,"
			+ "Store_idStore,CheckInOut)" + "VALUES(?,?,0);";
	
	/** Statement to validate user is not checked in already */
	static String validateIfUserIsCheckedInStatement = "SELECT count(*) "
			+ "as exist FROM CheckInOut where Customer_idCustomer = ? and CheckInOut = 0";

	/** Statement to update user data */
	static String updateUserDataStatement = "UPDATE Customer "
			+ "SET Name = ? " + ",PhoneNumber = ? " + ",EmailAddress = ? "
			+ ",Password = ? " + ",birthDay = ? "
			+ ",RoleTypes_idRoleTypes = ? " + "  where idCustomer = ? ";
	
	/** Statement de-activate a user */
	static String deleteUserStatement = "update Customer set Active = ? where idCustomer = ?";
	
	static String getUserTotalPesosAvailable = "SELECT (SUM(Credit)-SUM(Debit) - (SELECT COALESCE(SUM(Coupons.PointValue), 0)" 
	+ "as totalSum FROM CheckInOut LEFT JOIN Coupons on Coupons.idCoupons = Coupons_idCoupons WHERE Customer_idCustomer = ? AND CheckInOut = 0))" 
	+ "as totalCredit FROM PointLedger WHERE Customer_idCustomer = ?";
	
	// *************** STORE ****************** 
	
	/** Statement to get stores data */
	static String storeDataStatement = "SELECT * FROM Store";

	/** Statement to gets users checked in by store */
	static String getUserCheckedInByStoreDataStatement = "SELECT cko.Customer_idCustomer,cko.Store_idStore, "
			+ "		cko.idCheckInOut,cko.Coupons_idCoupons,cus.Name cusName,cus.phoneNumber,cus.EmailAddress, "
			+ "		str.Name storeName, cup.Name couponName, cup.PointValue "
			+ "		FROM CheckInOut  cko "
			+ "		left join Customer cus "
			+ "		on cus.idCustomer  = cko.Customer_idCustomer "
			+ "		left join Coupons cup  "
			+ "		on cup.idCoupons = cko.Coupons_idCoupons "
			+ "		left join Store str "
			+ "		on str.idStore = cko.Store_idStore "
			+ "		where  cko.CheckInOut = 0 and str.idStore = ?";
	
	/** Statement get a user checked in by store */
	static String getIndivUserCheckedInByDataStatement = "SELECT cko.Customer_idCustomer,cko.Store_idStore,"
			+ "cko.idCheckInOut,cko.Coupons_idCoupons,cus.Name cusName,cus.phoneNumber,cus.EmailAddress,"
			+ "		str.Name storeName, cup.Name couponName, cup.PointValue FROM CheckInOut  cko, Customer cus, Coupons cup ,Store str"
			+ "		where cus.idCustomer  = cko.Customer_idCustomer and cup.idCoupons = cko.Coupons_idCoupons"
			+ "		and str.idStore = cko.Store_idStore and cko.idCheckInOut = ?";
	
	/** Statement to add reward to check in table data */
	static String addSelectedRewardStatement = "UPDATE CheckInOut "
			+ "SET Coupons_idCoupons = ? "
			+ "WHERE idCheckInOut = ? ";
	
	/** Statement get check in location data */
	static String getCheckInLocationByCustIDStatement = "SELECT cko.Customer_idCustomer,cko.Store_idStore,"
			+ "cko.idCheckInOut,cko.Coupons_idCoupons "
			+ "		 FROM CheckInOut  cko "
			+ "		where cko.Customer_idCustomer = ? and CheckInOut = 0";
	
	/** Statement reset check in location data */
	static String resetCheckInLocationStatement = "delete from CheckInOut where Customer_idCustomer = ?";

	
	// *************** COUPON ****************** 
	
	/** Statement get a coupon */
	static String getCouponStatement = "SELECT * from Coupons where idCoupons = ? ";
	
	/** Statement add a coupon */
	static String addCouponStatement = "INSERT INTO Coupons( Name,PointValue,DollarOffValue,BeginDatetime,EndDatetime,Active,CreatedBy,birthdayPromo) "
			+ "VALUES(?,?,?,?,?,?,?,?)";
	
	/** Statement update a coupon */
	static String updateCouponStatement = "UPDATE Coupons  SET Name = ?,"
			+ " PointValue = ?,DollarOffValue = ?,BeginDatetime = ?,EndDatetime = ?,Active = ?,"
			+ " ModifiedBy = ? ,ModifiedDatetime = current_timestamp, birthdayPromo = ? WHERE idCoupons = ?";
	
	/** Statement delete a coupon */
	static String deleteCouponStatement = "delete from Coupons where idCoupons = ?";
	
	
	// *************** CAMPAIGN ****************** 
	
	/** Statement add a Campaign */
	static String addCampaignStatement = "INSERT INTO Campaign( Name,Description,PointMultiplier,BeginDatetime,EndDatetime,Active,CreatedBy,ModifiedBy,birthdayPromo) "
			+ "VALUES(?,?,?,?,?,?,?,?,?)";
	
	/** Statement update a Campaign */
	static String updateCampaignStatement = "UPDATE Campaign  SET Name = ?,"
			+ " Description = ?,PointMultiplier = ?,BeginDatetime = ?,EndDatetime = ?,Active = ?,"
			+ " ModifiedBy = ?,ModifiedDatetime = current_timestamp ,birthdayPromo = ? WHERE idCampaign = ?";
	
	/** Statement delete a Campaign */
	static String deleteCampaignStatement = "delete from Campaign where idCampaign = ?";
	
	/** Statement get all Campaign */
	static String getCampaignsStatement = "SELECT * FROM Campaign where 1 = 1";
	
	/** Statement get a Campaign */
	static String getCampaignDataStatement = "SELECT * from Campaign where idCampaign = ?";
	
	/** Statement get all customers */
	static String getCustomersStatement = "SELECT * FROM Customer where 1 = 1";
	
	/** Statement search for customers */
	static String searchCustomersDataStatement = "SELECT * FROM Customer WHERE (PhoneNumber = ? or emailAddress = ?)";
	
	String err = "";
	
	public String getError() {
		return err;
	}
		
	// ************ CONSTRUCTOR ******************************************************************
	
	/**
	 * Constructor creates connection to MySQL server and creates prepared statements 
	 */
	public DBHelper() {
		/*
		 * Set database parameters, establish connection, and create prepared
		 * statements
		 */
		
	}
	
	public Connection connection(){
			
		Connection cConn = null;
		
		/*
		 * Set database parameters, establish connection, and create prepared
		 * statements
		 */
		
		String db_url = "jdbc:mysql://ec2-50-19-53-184.compute-1.amazonaws.com:3306/frontera?noAccessToProcedureBodies=true&dontTrackOpenResources=true&connectTimeout=180000";
		String db_uname = "root";
		String db_password = "fr0nt3ra";

		/*
		 * if test is running check if contains test database connection
		 * properties and use
		 */
		if (System.getProperty("db_url") != null) {
			db_url = System.getProperty("db_url");
			db_uname = System.getProperty("db_uname");
			db_password = System.getProperty("db_password");
		}

		try {
			
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			cConn = DriverManager.getConnection(db_url,
					db_uname, db_password);

			System.out.println("Connected to MySQL!");

		} catch (Exception e) {
			System.out.println("Error in DBHelper constructor: "
					+ e.getClass().getName() + ": " + e.getMessage());
			String err = e.getClass().getName() + ": " + e.getMessage();
		}

		return cConn;
	}
	
	public static void disconnect(ResultSet rs, PreparedStatement ps, Connection cn){
		
		// CLOSE DB CONNECTIONS
		
		try{
            if(rs != null) rs.close();
            System.out.println("Success: RS disconnect");
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   


        try{
           if(ps != null) ps.close();
           System.out.println("Success: PS disconnect");
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   

        try{
            if(cn != null) cn.close();
            System.out.println("Success: CN disconnect");
            if (cn != null){
            	System.out.println("Success: CN not really disconnected");
            }
        }
        catch(SQLException sqlEx){
            System.out.println("Error: disconnect");
        }   
		
	}
	
	// *************** PREPARED STATEMENTS *******************************************************
	/**
	 * create the prepared statements separated for readability 
	 */
	private void createPreparedStatements(Connection connReceived) {
		
		Connection conn = connReceived;
		
	try{

		
			
	// *************** STORE ****************** 


			
			// STORED PROCEDURE CALL
			// completeCheckOut = (CallableStatement) conn
				//	.prepareCall("{call ledgerDo(?,?,?,?)}");

		
	}
	catch (Exception e){
		System.out.println("Error in constructor: "+e.getClass().getName()+":"+ e.getMessage());
	}		

}
	

	/**
	 * search for customer ID
	 * 
	 * @param phoneNumber
	 * @param emailAddress
	 */
	public int searchCustomerData(String phoneNumber, String emailAddress) {
		int userId = -1;
		PreparedStatement psSearchCustomersDataStatement = null;
		ResultSet rs = null;
		Connection rConn = connection();

		String cleanPhoneNumber = "";

		// remove everything but the numbers
		cleanPhoneNumber = phoneNumber.replaceAll("[^0-9]", "");

		// validate there are ten digits
		if (cleanPhoneNumber.length() == 10) {

			System.out.println(cleanPhoneNumber);

			try {
				
				psSearchCustomersDataStatement = rConn.prepareStatement(searchCustomersDataStatement);
				
				psSearchCustomersDataStatement.setString(1, cleanPhoneNumber);
				psSearchCustomersDataStatement.setString(2, emailAddress);

				System.out
						.println("run validateUserLogin query search customer");
				rs = psSearchCustomersDataStatement.executeQuery();
				while (rs.next()) {
					userId = rs.getInt("idCustomer");

				}
			}

			catch (Exception e) {
				System.out.println("Error in searchCustomersDataStatement: "
						+ e.getClass().getName() + ":" + e.getMessage());
				userId = -2;
				
			} finally {
				DBHelper.disconnect(rs, psSearchCustomersDataStatement, rConn);
			}
		}
		return userId;
	}
	
	
	
	
	                                     

		/** Check the user out 
		 * @param int checkInID
		 * @param String subTotal
		 * @param String ticketId 
		 * @param int customerID 
		 * @param  serverID
		 *		 
		 */
	public int checkUserOut(int checkInID, String subTotal, String ticketId,int customerID, int serverID){
		int intCheckUserOut = 0;
		double cSubTotal = 0;
		
		java.sql.CallableStatement completeCheckOut = null;
		ResultSet rs = null;
		Connection rConn = connection();
		
		try{
			
			cSubTotal = Double.parseDouble(subTotal);
			
			completeCheckOut = rConn.prepareCall("{call ledgerDo(?, ?, ?, ?)}");
			
			System.out.println(checkInID);
			System.out.println(subTotal);
			System.out.println(ticketId);
			System.out.println(customerID);
			System.out.println(serverID);
			System.out.println("insert check out");
			
			completeCheckOut.setInt(1, customerID);
			completeCheckOut.setDouble(2, cSubTotal);
			completeCheckOut.setString(3, ticketId);
			completeCheckOut.setInt(4, serverID);
			rs = completeCheckOut.executeQuery();
			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in check user out: "+ e.getClass().getName()+":"+e.getMessage());
			intCheckUserOut = 9;
		} finally {
			DBHelper.disconnect(rs, completeCheckOut, rConn);
		}
		return intCheckUserOut;
	}
	
	
	public static Cookie eraseCookie(String strCookieName, String strPath) {
	    Cookie cookie = new Cookie(strCookieName, "");
	    cookie.setMaxAge(0);
	    cookie.setPath(strPath);

	    return cookie;
	}
	
	/** Calculates user's total pesos by querying PointLedger and looking to see if there are any pending coupons in checkinout
	 * @param userID
	 * @return user's total pesos
	 */
	private int getUserPesos(String userID){
		int totalPesos = 0;
		
		Connection gConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetUserTotalPesosAvailable = null;
		
		try {
			
			psGetUserTotalPesosAvailable = gConn.prepareStatement(getUserTotalPesosAvailable);
			psGetUserTotalPesosAvailable.setString(1, userID);
			psGetUserTotalPesosAvailable.setString(2, userID);
			rs = psGetUserTotalPesosAvailable.executeQuery();
			
			while (rs.next()){
				totalPesos = rs.getInt("totalCredit");
			}
			
		} catch (Exception e){
			System.out.println("Error in getUserPesos Statement: "+e.getClass().getName()+":"+ e.getMessage());
	    } finally {
	    	// CLOSE DB CONNECTIONS	
	    	DBHelper.disconnect(rs, psGetUserTotalPesosAvailable, gConn);
		}
		return totalPesos;
	}
	
	
	
	
	/** Returns the user data */
	public ArrayList<UserData> getUserInfo (String userID){
		ArrayList<UserData> userInfoData = new ArrayList<UserData>();
		
		Connection guiConn = connection();
		ResultSet rs = null;
		PreparedStatement psUserDataStatement = null;
		
		try {
			System.out.println("start query");
			
			psUserDataStatement = guiConn.prepareStatement(userDataStatement);
			psUserDataStatement.setString(1, FormatAndValidation.cleanPhoneNumber(userID));
			rs = psUserDataStatement.executeQuery();
			System.out.println("display id " + FormatAndValidation.cleanPhoneNumber(userID));
			while (rs.next()){
				System.out.println("display getUserInfo query");				
			//	`customer`.`idCustomer`, `customer`.`Name`, `customer`.`PhoneNumber`, `customer`.`EmailAddress`, `customer`.`Password`,userActive
				//`customer`.`CreatedDatetime`, `customer`.`ModifiedDatetime`, `customer`.`Active`
				
				UserData newUserData = new UserData(rs.getString("PhoneNumber"),rs.getString("Name"),rs.getString("phoneNumber"),rs.getInt("idCustomer"),rs.getString("EmailAddress"),getUserPesos(rs.getString("idCustomer")),rs.getString("Password"),rs.getString("birthDay"),rs.getInt("Active"),rs.getInt("RoleTypes_idRoleTypes"));
				userInfoData.add(newUserData);
			}
			rs.close();
	}
		
		catch (Exception e){
			System.out.println("Error in userDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    } finally {
		    	// CLOSE DB CONNECTIONS	
		    	DBHelper.disconnect(rs, psUserDataStatement, guiConn);
						
				
			}
		System.out.println("end query");
		return userInfoData;
	}
	/** Returns the all Customer info data */
	public ArrayList<UserData> getAllCustomerInfo(){
		ArrayList<UserData> customerData = new ArrayList<UserData>();
		
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetCustomersStatement = null;
		
		try {
			System.out.println("start query");
			
			psGetCustomersStatement = cConn.prepareStatement(getCustomersStatement);
			
			rs = psGetCustomersStatement.executeQuery();
			while (rs.next()){
				System.out.println("display getAllCustomerInfo query");				
			//	`customer`.`idCustomer`, `customer`.`Name`, `customer`.`PhoneNumber`, `customer`.`EmailAddress`, `customer`.`Password`,
				//`customer`.`CreatedDatetime`, `customer`.`ModifiedDatetime`, `customer`.`Active`
				
				UserData newUserData = new UserData(rs.getString("PhoneNumber"),rs.getString("Name"),rs.getString("phoneNumber"),rs.getInt("idCustomer"),rs.getString("EmailAddress"),getUserPesos(rs.getString("idCustomer")),rs.getString("Password"),rs.getString("birthDay"),rs.getInt("Active"),rs.getInt("RoleTypes_idRoleTypes"));
				customerData.add(newUserData);
			}
	}
		
		catch (Exception e){
			System.out.println("Error in getAllCustomerInfo: "+e.getClass().getName()+":"+ e.getMessage());
		    } finally {
		    	DBHelper.disconnect(rs, psGetCustomersStatement, cConn);
		    }
		System.out.println("end query");
		return customerData;
	}
	
	
	
	/** Returns the store data */
	public ArrayList<StoreLocation> getStoreInfo (){
		ArrayList<StoreLocation> storeInfoData = new ArrayList<StoreLocation>();
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psStoreDataStatement = null;
		
		try {
			System.out.println("start store query");
			
			psStoreDataStatement = cConn.prepareStatement(storeDataStatement);
			
			rs = psStoreDataStatement.executeQuery();
			while (rs.next()){
				System.out.println("display  store query");				
			

				StoreLocation newStoreData = new StoreLocation(rs.getString("Name"),rs.getString("Address1"),rs.getString("Address2"),rs.getString("City"),rs.getString("Zipcode"),rs.getString("PhoneNumber"),rs.getString("State"),"45",rs.getInt("idStore"),"1");
				storeInfoData.add(newStoreData);
			}
			
	}
		
		catch (Exception e){
			System.out.println("Error in storeDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    } finally {
		    	DBHelper.disconnect(rs, psStoreDataStatement, cConn);
		    }
		System.out.println("end store query");
		return storeInfoData;
	}
	
	/** De-activate user data */
	public int deactivateUser(int custID,int activeStatus){
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psDeleteUserStatement = null;
		
		try{
			//TODO: change Active status to 1 and deactivate status to 0 in the Database
			
			psDeleteUserStatement = cConn.prepareStatement(deleteUserStatement);
			
			psDeleteUserStatement.setInt(1, activeStatus);
			psDeleteUserStatement.setInt(2, custID);
			
						
			psDeleteUserStatement.executeUpdate();
			
			System.out.println(validate);
		
		}
	
		catch (Exception e) {
			
			System.out.println("Error in deleteUserStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, psDeleteUserStatement, cConn);
		}
		return validate;
	}
	
	/** Returns a list of users that are checked in by store data */
	public ArrayList<UserCheckInData> getCheckedInUsersinfo (int storeID){
		ArrayList<UserCheckInData> CheckedInUsersInfoData = new ArrayList<UserCheckInData>();
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetUserCheckedInByStoreDataStatement = null;
		
		try {
			System.out.println("start query");
			
			psGetUserCheckedInByStoreDataStatement = cConn.prepareStatement(getUserCheckedInByStoreDataStatement);
			
			psGetUserCheckedInByStoreDataStatement.setInt(1, storeID);
			
			rs = psGetUserCheckedInByStoreDataStatement.executeQuery();
			System.out.println("display checked in users " );
			ArrayList<UserReward> userRewd = new ArrayList<UserReward>();
			
			while (rs.next()){
				System.out.println("display query" +rs.getString("PointValue"));				
				
				UserCheckInData newCheckInData = new UserCheckInData(rs.getString("phoneNumber"),rs.getInt("Customer_idCustomer"),rs.getString("cusName"),rs.getString("EmailAddress")
						,rs.getString("phoneNumber"),rs.getInt("idCheckInOut"),userRewd,getUserPesos(rs.getString("Customer_idCustomer")),"no image",rs.getString("storeName")
						,rs.getInt("Store_idStore"),rs.getString("couponName"),rs.getInt("Coupons_idCoupons"),rs.getInt("PointValue"));
				
				 CheckedInUsersInfoData.add(newCheckInData);
			}
			
	}
		catch (Exception e){
			System.out.println("Error in getUserCheckedInByStoreDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    } finally {
		    	DBHelper.disconnect(rs, psGetUserCheckedInByStoreDataStatement, cConn);
		    }
		System.out.println("end query");
		return CheckedInUsersInfoData;
	}
	
	/** Returns a specific users by ID that's checked in data */
	public ArrayList<UserCheckInData> getIndivCheckedInUserInfo (int checkInID){
		ArrayList<UserCheckInData> CheckedInUsersInfoData = new ArrayList<UserCheckInData>();
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetIndivUserCheckedInByDataStatement = null;		
		
		try {
			System.out.println("start query");
			
			psGetIndivUserCheckedInByDataStatement = cConn.prepareStatement(getIndivUserCheckedInByDataStatement);
			
			psGetIndivUserCheckedInByDataStatement.setInt(1, checkInID);
			
			rs = psGetIndivUserCheckedInByDataStatement.executeQuery();
			System.out.println("display checked in users " );
			ArrayList<UserReward> userRewd = new ArrayList<UserReward>();

			while (rs.next()){
				System.out.println("display query" +rs.getString("PointValue"));				
				
				UserCheckInData newCheckInData = new UserCheckInData(rs.getString("phoneNumber"),rs.getInt("Customer_idCustomer"),rs.getString("cusName"),rs.getString("EmailAddress")
						,rs.getString("phoneNumber"),rs.getInt("idCheckInOut"),userRewd,getUserPesos(rs.getString("Customer_idCustomer")),"no image",rs.getString("storeName")
						,rs.getInt("Store_idStore"),rs.getString("couponName"),rs.getInt("Coupons_idCoupons"),rs.getInt("PointValue"));
				
				 CheckedInUsersInfoData.add(newCheckInData);
			}
			
	}
		
		catch (Exception e){
			System.out.println("Error in getIndivUserCheckedInByDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    } finally {
		    	DBHelper.disconnect(rs, psGetIndivUserCheckedInByDataStatement, cConn);
		    }
		System.out.println("end query");
		return CheckedInUsersInfoData;
	}
	/** get Customer Store CheckIn Location by using customer id  */
	public int getCustStoreCheckInLocation(int custID){
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetCheckInLocationByCustIDStatement = null;
		
		try{
			
			psGetCheckInLocationByCustIDStatement = cConn.prepareStatement(getCheckInLocationByCustIDStatement);
			
			psGetCheckInLocationByCustIDStatement.setInt(1, custID);
			
			rs = psGetCheckInLocationByCustIDStatement.executeQuery();
			while (rs.next()){
				validate = rs.getInt("Store_idStore");//get store id
				System.out.println(" in the process "+      rs.getInt("Store_idStore"));
			}
			System.out.println(validate);			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in getCheckInLocationStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, psGetCheckInLocationByCustIDStatement, cConn);
		}
		return validate;
	}
	/** get check in ID using cust ID*/
	public int getCustCheckInID(int custID){
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetCheckInLocationByCustIDStatement = null;
		
		try{
			
			psGetCheckInLocationByCustIDStatement = cConn.prepareStatement(getCheckInLocationByCustIDStatement);
			
			psGetCheckInLocationByCustIDStatement.setInt(1, custID);
			
			rs = psGetCheckInLocationByCustIDStatement.executeQuery();
			while (rs.next()){
				validate = rs.getInt("idCheckInOut");//get checkin id
				System.out.println(" in the process "+      rs.getInt("idCheckInOut"));
			}
			System.out.println(validate);

		}
	
		catch (Exception e) {
			
			System.out.println("Error in getCustCheckInID: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, psGetCheckInLocationByCustIDStatement, cConn);
		}
		return validate;
	}
	
	/** get current user check in data */
		public ArrayList<UserCheckInData> getUserCheckInData(int custID){
		int validate = 0;
		ArrayList<UserCheckInData> CheckedInUsersInfoData = new ArrayList<UserCheckInData>();
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psGetCheckInLocationByCustIDStatement = null;
		
		try{
			
			psGetCheckInLocationByCustIDStatement = cConn.prepareStatement(getCheckInLocationByCustIDStatement);
			
			psGetCheckInLocationByCustIDStatement.setInt(1, custID);
			
			rs = psGetCheckInLocationByCustIDStatement.executeQuery();
			while (rs.next()){
				//validate = rs.getInt("Store_idStore");//get store id
				
				UserCheckInData newCheckInData = new UserCheckInData(rs.getInt("Customer_idCustomer"),rs.getInt("idCheckInOut"),rs.getInt("Store_idStore"),rs.getInt("Coupons_idCoupons"));
				
				CheckedInUsersInfoData.add(newCheckInData);
				System.out.println(" in the process ");
			}
		}
	
		catch (Exception e) {
			
			System.out.println("Error in getCheckInLocationByCustIDStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, psGetCheckInLocationByCustIDStatement, cConn);
		}
		return CheckedInUsersInfoData;
	}
	
	/** check if user is already checked in */
	public int validateIfUserIsCheckedIn(int custID){
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement cValidateIfUserIsCheckedInStatement = null;
		
		try{
			
			cValidateIfUserIsCheckedInStatement = cConn.prepareStatement(validateIfUserIsCheckedInStatement);
			
					cValidateIfUserIsCheckedInStatement.setInt(1, custID);
			
			rs = cValidateIfUserIsCheckedInStatement.executeQuery();
			while (rs.next()){
				validate = rs.getInt("exist");//see is userExist already
				System.out.println(" in the process "+      rs.getInt("exist"));
			}
			System.out.println(validate);

		}
	
		catch (Exception e) {
			
			System.out.println("Error in validateIfUserIsCheckedInStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, cValidateIfUserIsCheckedInStatement, cConn);
		}
		return validate;
	}
	/** reset check in location*/
	public int resetCheckInLocation(int custID){
		int validate = 0;Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psResetCheckInLocationStatement = null;
		
		try{
			
			psResetCheckInLocationStatement = cConn.prepareStatement(resetCheckInLocationStatement);
			
			psResetCheckInLocationStatement.setInt(1, custID);
			
			psResetCheckInLocationStatement.executeUpdate();
			
			System.out.println(validate);

		}
	
		catch (Exception e) {
			
			System.out.println("Error in resetCheckInLocationStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, psResetCheckInLocationStatement, cConn);
		}
		return validate;
	}
	/** check user in */
	public int checkUserIn(int custID, int storeID){
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement cCheckUserInStatement = null;
		
		try{
			
			cCheckUserInStatement = cConn.prepareStatement(checkUserInStatement);
			
			cCheckUserInStatement.setInt(1, custID);
			cCheckUserInStatement.setInt(2, storeID);
			
			validate = validateIfUserIsCheckedIn(custID);
			
			if(validate == 0)
				{
			
					System.out.println(custID);
					System.out.println(storeID);
					System.out.println("larry test");
					cCheckUserInStatement.executeUpdate();
				}
			else
			{
				validate = 10;
			}

		}
	
		catch (Exception e) {
			
			System.out.println("Error in checkUserInStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, cCheckUserInStatement, cConn);
		}
		return validate;
	}
	
	/** add the user selected reward */
	public int addSelectedReward(int coupID, int checkInID){
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement psAddSelectedRewardStatement = null;
		
		try{
			
			psAddSelectedRewardStatement = cConn.prepareStatement(addSelectedRewardStatement);
			
			psAddSelectedRewardStatement.setInt(1, coupID);
			psAddSelectedRewardStatement.setInt(2, checkInID);
			
			System.out.println(coupID);
			System.out.println(checkInID);
			
			System.out.println("larry test checkInID");
			psAddSelectedRewardStatement.executeUpdate();
			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in addSelectedRewardStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, psAddSelectedRewardStatement, cConn);
		}
		return validate;
	}
	
	
	/** Update the user data */
	public int updateUserData(String fullName, String userPhoneNumber, String userEmailAddress,
	   		 String userPassword, String userBirthDay, int custID,int role){
		//role 3=mgmt 2=server 1=customer
		int validate = 0;
		Connection cConn = connection();
		ResultSet rs = null;
		PreparedStatement cUpdateUserDataStatement = null;
		
		
		try{
			
			cUpdateUserDataStatement = cConn.prepareStatement(updateUserDataStatement);
			
			System.out.println(fullName);
			System.out.println(userPhoneNumber);
			System.out.println(userEmailAddress);	
			System.out.println(userPassword);
			System.out.println("update user test");
			System.out.println(userBirthDay);
			System.out.println(role);
			System.out.println(custID);
							
			cUpdateUserDataStatement.setString(1, fullName);
			cUpdateUserDataStatement.setString(2, userPhoneNumber);
			cUpdateUserDataStatement.setString(3, userEmailAddress);
			cUpdateUserDataStatement.setString(4, userPassword);
			cUpdateUserDataStatement.setString(5, userBirthDay);
			cUpdateUserDataStatement.setInt(6, role);
			cUpdateUserDataStatement.setInt(7, custID);
			cUpdateUserDataStatement.executeUpdate();
				
		}
	
		catch (Exception e) {
			
			System.out.println("Error in updateUserDataStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		} finally {
			DBHelper.disconnect(rs, cUpdateUserDataStatement, cConn);
		}
		return validate;
	}
	
	

	

	
	
}
